﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{

    public enum EntityTypesEnum
    {
        Project = 122, // 64 @ Wafik
        Contact = 121, // 65 @ Wafik
        Item = 123, // 66 @ Wafik
        Other = 124
    }

    public enum LanguagesEnum
    {
        English = 1033,
        Arabic = 1025,
        French = 1036
    }


    public enum CategoriesEnum
    {
        Entity = 1,
        NodeTitle = 14,
        EntityType = 13,
        //NodeContentType = 4
    }

    public enum ContentTypesEnum
    {
        Content1 = 93,
        Content2 = 94,
        EntityType = 8
    }
    public partial class DataManager
    {



        #region Select

        public static List<CustomSiteMapNode> GetSiteMapNodes(int languageCode)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetSiteMapNodesWithAllLanguages";

            #region Parametrers
            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = languageCode,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@EnglishLanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = (int)LanguagesEnum.English,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ArabicLanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = (int)LanguagesEnum.Arabic,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@FrenchLanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = (int)LanguagesEnum.French,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@EntityCategoryId",
                SqlDbType = SqlDbType.Int,
                Value = (int)CategoriesEnum.Entity,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@TitleCategoryId",
                SqlDbType = SqlDbType.Int,
                Value = 14, //(int)CategoriesEnum.NodeTitle,
            });

            #endregion

            _DataSet = new DataSet();
            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            List<CustomSiteMapNode> nodes = new List<CustomSiteMapNode>();

            DataTable siteMapNodesTable = _DataSet.Tables[0];
            if (siteMapNodesTable != null)
            {
                #region GetSitemapNodes

                for (int i = 0; i < siteMapNodesTable.Rows.Count; i++)
                {
                    CustomSiteMapNode siteMapNode = new CustomSiteMapNode();
                    if (siteMapNodesTable.Rows[i]["SiteMapNodeId"] != null)
                    {
                        int siteMapNodeId;
                        if (int.TryParse(siteMapNodesTable.Rows[i]["SiteMapNodeId"].ToString(), out siteMapNodeId) &&
                            siteMapNodeId > 0)
                        {
                            siteMapNode.SiteMapNodeId = siteMapNodeId;
                        }
                        else
                            continue;
                    }

                    if (siteMapNodesTable.Rows[i]["TitleId"] != null && siteMapNodesTable.Rows[i]["TitleName"] != null)
                    {
                        siteMapNode.Title = new TinyItem();
                        siteMapNode.TitleItem = new Item();
                        int itemTitleId;
                        if (int.TryParse(siteMapNodesTable.Rows[i]["TitleId"].ToString(), out itemTitleId) &&
                            itemTitleId > 0)
                        {
                            siteMapNode.Title.ID = itemTitleId;
                            siteMapNode.TitleItem.ItemID = itemTitleId;
                        }
                        if (siteMapNodesTable.Rows[i]["TitleName"] != null)
                        {
                            siteMapNode.Title.Name = siteMapNodesTable.Rows[i]["TitleName"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["TitleEnglishName"] != null)
                        {
                            siteMapNode.TitleItem.EnglishName = siteMapNodesTable.Rows[i]["TitleEnglishName"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["TitleArabicName"] != null)
                        {
                            siteMapNode.TitleItem.ArabicName = siteMapNodesTable.Rows[i]["TitleArabicName"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["TitleFrenchName"] != null)
                        {
                            siteMapNode.TitleItem.FrenchName = siteMapNodesTable.Rows[i]["TitleFrenchName"].ToString();
                        }
                    }

                    if (siteMapNodesTable.Rows[i]["Url"] != null)
                    {
                        siteMapNode.Url = siteMapNodesTable.Rows[i]["Url"].ToString();
                    }


                    if (siteMapNodesTable.Rows[i]["EntityId"] != null && siteMapNodesTable.Rows[i]["EntityName"] != null)
                    {
                        siteMapNode.Entity = new TinyItem();
                        siteMapNode.EntityItem = new Item();
                        int itemTitleId;
                        if (int.TryParse(siteMapNodesTable.Rows[i]["EntityId"].ToString(), out itemTitleId) &&
                            itemTitleId > 0)
                        {
                            siteMapNode.Entity.ID = itemTitleId;
                        }
                        int parentItemId;
                        if (int.TryParse(siteMapNodesTable.Rows[i]["EntityParentItemId"].ToString(), out parentItemId) &&
                          parentItemId > 0)
                        {
                            siteMapNode.EntityItem.ParentItemId = parentItemId;
                        }
                        if (siteMapNodesTable.Rows[i]["EntityName"] != null)
                        {
                            siteMapNode.Entity.Name = siteMapNodesTable.Rows[i]["EntityName"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["EntityEnglishName"] != null)
                        {
                            siteMapNode.EntityItem.EnglishName = siteMapNodesTable.Rows[i]["EntityEnglishName"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["EntityArabicName"] != null)
                        {
                            siteMapNode.EntityItem.ArabicName = siteMapNodesTable.Rows[i]["EntityArabicName"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["EntityFrenchName"] != null)
                        {
                            siteMapNode.EntityItem.FrenchName = siteMapNodesTable.Rows[i]["EntityFrenchName"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["EntityEnglishDescription"] != null)
                        {
                            siteMapNode.EntityItem.EnglishDescription = siteMapNodesTable.Rows[i]["EntityEnglishDescription"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["EntityArabicDescription"] != null)
                        {
                            siteMapNode.EntityItem.ArabicDescription = siteMapNodesTable.Rows[i]["EntityArabicDescription"].ToString();
                        }
                        if (siteMapNodesTable.Rows[i]["EntityFrenchDescription"] != null)
                        {
                            siteMapNode.EntityItem.FrenchDescription = siteMapNodesTable.Rows[i]["EntityFrenchDescription"].ToString();
                        }
                    }

                    int level;
                    if (int.TryParse(siteMapNodesTable.Rows[i]["Level"].ToString(), out level) &&
                           level > 0)
                    {
                        siteMapNode.Level = level;
                    }

                    int entityTypeId;
                    if (int.TryParse(siteMapNodesTable.Rows[i]["EntityTypeId"].ToString(), out entityTypeId) &&
                           entityTypeId > 0)
                    {
                        siteMapNode.EntityTypeId = entityTypeId;
                    }

                    int parentId;
                    int.TryParse(siteMapNodesTable.Rows[i]["ParentId"].ToString(), out parentId);
                    siteMapNode.ParentId = parentId;

                    bool isContentNode = false;
                    bool.TryParse(siteMapNodesTable.Rows[i]["IsContentNode"].ToString(), out isContentNode);
                    siteMapNode.IsContentNode = isContentNode;

                    nodes.Add(siteMapNode);
                }
                #endregion

            }

            DataTable siteMapNodesContentTable = _DataSet.Tables.Count > 1 ? _DataSet.Tables[1] : null;

            List<SitemapNodeContent> sitemapNodeContents = new List<SitemapNodeContent>();
            if (siteMapNodesContentTable != null)
            {
                #region GetContentes

                for (int i = 0; i < siteMapNodesContentTable.Rows.Count; i++)
                {
                    SitemapNodeContent sitemapNodeContent = new SitemapNodeContent();
                    if (siteMapNodesContentTable.Rows[i]["SiteMapNodeId"] != null)
                    {
                        int siteMapNodeContentId;
                        if (int.TryParse(siteMapNodesContentTable.Rows[i]["SiteMapNodeContentId"].ToString(), out siteMapNodeContentId) &&
                            siteMapNodeContentId > 0)
                        {
                            sitemapNodeContent.SiteMapNodeContentId = siteMapNodeContentId;
                        }
                        else
                            continue;
                    }

                    int siteMapNodeId;
                    if (int.TryParse(siteMapNodesContentTable.Rows[i]["SiteMapNodeId"].ToString(), out siteMapNodeId) &&
                        siteMapNodeId > 0)
                    {
                        sitemapNodeContent.SiteMapNodeId = siteMapNodeId;
                    }

                    int contentTypeId;
                    if (int.TryParse(siteMapNodesContentTable.Rows[i]["ContentTypeId"].ToString(), out contentTypeId) &&
                        contentTypeId > 0)
                    {
                        sitemapNodeContent.ContentTypeId = contentTypeId;
                    }

                    if (siteMapNodesContentTable.Rows[i]["Content"] != null)
                    {
                        sitemapNodeContent.Content = siteMapNodesContentTable.Rows[i]["Content"].ToString();
                    }

                    int contentlanguageCode;
                    if (int.TryParse(siteMapNodesContentTable.Rows[i]["LanguageCode"].ToString(), out contentlanguageCode) &&
                        contentlanguageCode > 0)
                    {
                        sitemapNodeContent.LanguageCode = contentlanguageCode;
                    }
                    sitemapNodeContents.Add(sitemapNodeContent);
                }
                #endregion

            }

            PrepareNestedNodes(nodes, sitemapNodeContents);
            return nodes;
        }

        private static void PrepareNestedNodes(List<CustomSiteMapNode> nodes, List<SitemapNodeContent> sitemapNodeContents)
        {
            var childNodes = nodes.Where(n => n.ParentId > 0).ToList();
            foreach (CustomSiteMapNode item in nodes)
            {
                CustomSiteMapNode node = item;
                node.NestedNodes = childNodes.Where(n => n.ParentId == node.SiteMapNodeId).ToList();
                node.SitemapNodeContents = sitemapNodeContents.Where(content => content.SiteMapNodeId == node.SiteMapNodeId).ToList();
            }
        }

        public static List<Item> GetItemsByLanguageAndCategory(int languageCode, int? categoryId, int? itemId = null)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetItemsByLanguageAndCategory";

            _Command.Parameters.Clear();

            if (categoryId != null && categoryId.Value > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                                                {
                                                    ParameterName = "@CategoryId",
                                                    SqlDbType = SqlDbType.Int,
                                                    Value = categoryId.Value,
                                                });
            }

            if (itemId != null && itemId.Value > 0)
            {

                _Command.Parameters.Add(new SqlParameter
                                                {
                                                    ParameterName = "@itemId",
                                                    SqlDbType = SqlDbType.Int,
                                                    Value = itemId,
                                                });
            }

            _Command.Parameters.Add(new SqlParameter
                                            {
                                                ParameterName = "@LanguageCode",
                                                SqlDbType = SqlDbType.Int,
                                                Value = languageCode,
                                            });


            _DataSet = new DataSet();
            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            List<Item> items = new List<Item>();

            DataTable itemsTable = _DataSet.Tables[0];



            if (itemsTable != null)
            {

                // addition by joe
                //itemsTable.Columns["Name"].ColumnName = "itemName";

                for (int i = 0; i < itemsTable.Rows.Count; i++)
                {

                    if (itemsTable.Rows[i]["itemId"] != null && itemsTable.Rows[i]["itemName"] != null && itemsTable.Rows[i]["ItemCategoryId"] != null)
                    {
                        Item item = new Item();
                        int resultItemId;
                        if (int.TryParse(itemsTable.Rows[i]["itemId"].ToString(), out resultItemId) && resultItemId > 0)
                        {
                            item.ItemID = resultItemId;
                        }
                        item.Name = itemsTable.Rows[i]["itemName"].ToString();

                        int resultCatId;
                        if (int.TryParse(itemsTable.Rows[i]["ItemCategoryId"].ToString(), out resultCatId) && resultCatId > 0)
                        {
                            item.CategoryId = resultCatId;
                        }
                        items.Add(item);
                    }
                }
            }
            return items;
        }

        #endregion

        #region Insert

        public static int InsertEntity(Item entity)
        {
            int itemId = 0;
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertMasterItem";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CategoryId",
                SqlDbType = SqlDbType.Int,
                Value = CategoriesEnum.Entity,
            });
            _Command.Parameters.Add(new SqlParameter
           {
               ParameterName = "@ParentItemId",
               SqlDbType = SqlDbType.Int,
               Value = entity.ParentItemId,
           });

            _Adapter.InsertCommand = _Command;

            try
            {
                #region InsertMasterItem
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();



                if (returnedValue != null)
                {
                    int.TryParse(returnedValue.ToString(), out itemId);
                }
                #endregion

                #region InsertItemDetails
                if (itemId > 0)
                {
                    Dictionary<int, KeyValuePair<string, string>> itemDetails = new Dictionary<int, KeyValuePair<string, string>>();
                    itemDetails.Add((int)LanguagesEnum.English, new KeyValuePair<string, string>(entity.EnglishName, entity.EnglishDescription));
                    itemDetails.Add((int)LanguagesEnum.Arabic, new KeyValuePair<string, string>(entity.ArabicName, entity.ArabicDescription));
                    itemDetails.Add((int)LanguagesEnum.French, new KeyValuePair<string, string>(entity.FrenchName, entity.FrenchDescription));

                    foreach (var detail in itemDetails)
                    {
                        _Command.CommandType = CommandType.StoredProcedure;
                        _Command.CommandText = "InsertDetailItem";

                        _Command.Parameters.Clear();


                        _Command.Parameters.Add(new SqlParameter
                        {
                            ParameterName = "@ItemId",
                            SqlDbType = SqlDbType.Int,
                            Value = itemId,
                        });


                        _Command.Parameters.Add(new SqlParameter
                        {
                            ParameterName = "@LanguageCode",
                            SqlDbType = SqlDbType.Int,
                            Value = detail.Key,
                        });


                        _Command.Parameters.Add(new SqlParameter
                        {
                            ParameterName = "@Name",
                            SqlDbType = SqlDbType.NVarChar,
                            Value = detail.Value.Key,
                        });

                        _Command.Parameters.Add(new SqlParameter
                        {
                            ParameterName = "@Description",
                            SqlDbType = SqlDbType.NVarChar,
                            Value = detail.Value.Value,
                        });

                        _Adapter.InsertCommand = _Command;

                        // Open Connection
                        if (_Connection.State != ConnectionState.Open)
                            _Connection.Open();

                        _Command.ExecuteNonQuery();

                        // Close Connection)
                        if (_Connection.State != ConnectionState.Closed)
                            _Connection.Close();
                    }
                }
                #endregion
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            return itemId;
        }

        public static int InsertTitle(Item title)
        {
            int itemId = 0;
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertMasterItem";

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@CategoryId",
                SqlDbType = SqlDbType.Int,
                Value = CategoriesEnum.NodeTitle,
            });

            _Adapter.InsertCommand = _Command;

            try
            {
                #region InsertMasterItem
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();



                if (returnedValue != null)
                {
                    int.TryParse(returnedValue.ToString(), out itemId);
                }
                #endregion

                #region InsertItemDetails
                if (itemId > 0)
                {
                    Dictionary<int, string> itemDetails = new Dictionary<int, string>();
                    itemDetails.Add((int)LanguagesEnum.English, title.EnglishName);
                    itemDetails.Add((int)LanguagesEnum.Arabic, title.ArabicName);
                    itemDetails.Add((int)LanguagesEnum.French, title.FrenchName);

                    foreach (var detail in itemDetails)
                    {
                        _Command.CommandType = CommandType.StoredProcedure;
                        _Command.CommandText = "InsertDetailItem";

                        _Command.Parameters.Clear();


                        _Command.Parameters.Add(new SqlParameter
                        {
                            ParameterName = "@ItemId",
                            SqlDbType = SqlDbType.Int,
                            Value = itemId,
                        });


                        _Command.Parameters.Add(new SqlParameter
                        {
                            ParameterName = "@LanguageCode",
                            SqlDbType = SqlDbType.Int,
                            Value = detail.Key,
                        });


                        _Command.Parameters.Add(new SqlParameter
                        {
                            ParameterName = "@Name",
                            SqlDbType = SqlDbType.NVarChar,
                            Value = detail.Value,
                        });


                        _Adapter.InsertCommand = _Command;

                        // Open Connection
                        if (_Connection.State != ConnectionState.Open)
                            _Connection.Open();

                        _Command.ExecuteNonQuery();

                        // Close Connection)
                        if (_Connection.State != ConnectionState.Closed)
                            _Connection.Close();
                    }
                }
                #endregion

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            return itemId;
        }

        public static int InsertNewNode(CustomSiteMapNode node)
        {
            int sitemapNodeId = 0;
            int entityId = 0;

            if (!node.IsContentNode)
            {
                entityId = InsertEntity(node.EntityItem);
                if (entityId == 0)
                    return 0;

                node.Url += entityId;
            }

            int titleId = InsertTitle(node.TitleItem);
            if (titleId == 0)
                return 0;
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertSiteMapNode";

            #region Parameters


            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@TitleId",
                SqlDbType = SqlDbType.Int,
                Value = titleId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Url",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.Url,
            });
            if (entityId > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                   {
                       ParameterName = "@EntityId",
                       SqlDbType = SqlDbType.Int,
                       Value = entityId,
                   });
            }
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ParentSiteMapNodeId",
                SqlDbType = SqlDbType.Int,
                Value = node.ParentId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Level",
                SqlDbType = SqlDbType.Int,
                Value = node.Level,
            });
            if (node.EntityTypeId > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                                            {
                                                ParameterName = "@EntityTypeId",
                                                SqlDbType = SqlDbType.Int,
                                                Value = node.EntityTypeId,
                                            });
            }
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@IsContentNode",
                SqlDbType = SqlDbType.Bit,
                Value = node.IsContentNode,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();



                if (returnedValue != null)
                {
                    int.TryParse(returnedValue.ToString(), out sitemapNodeId);
                }

                node.SiteMapNodeId = sitemapNodeId;
                if (node.IsContentNode && sitemapNodeId > 0)
                {
                    node.Url += sitemapNodeId;
                    EditNodeUrl(node);
                }
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            return sitemapNodeId;

        }


        #endregion

        #region Save
        public static int SaveSiteMapNode(CustomSiteMapNode node)
        {
            int nodeId;
            if (node.SiteMapNodeId > 0)
            {
                EditNode(node);
                nodeId = node.SiteMapNodeId;
            }
            else
            {
                nodeId = InsertNewNode(node);
            }
            SaveNodeContents(nodeId, node.SitemapNodeContents);
            return nodeId;
        }

        private static void SaveNodeContents(int nodeId, List<SitemapNodeContent> sitemapNodeContents)
        {
            foreach (SitemapNodeContent item in sitemapNodeContents)
            {
                item.SiteMapNodeId = nodeId;
                if (item.SiteMapNodeContentId > 0)
                {
                    EditContent(item);
                }
                else
                {
                    InsertContent(item);
                }
            }
        }

        private static int InsertContent(SitemapNodeContent item)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "InsertSiteMapNodeContent";

            #region Parameters

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
                                        {
                                            ParameterName = "SiteMapNodeId",
                                            SqlDbType = SqlDbType.Int,
                                            Value = item.SiteMapNodeId,
                                        });
            _Command.Parameters.Add(new SqlParameter
                                        {
                                            ParameterName = "@Content",
                                            SqlDbType = SqlDbType.NVarChar,
                                            Value = item.Content,
                                        });
            _Command.Parameters.Add(new SqlParameter
                                        {
                                            ParameterName = "@ContentTypeId",
                                            SqlDbType = SqlDbType.Int,
                                            Value = item.ContentTypeId,
                                        });
            _Command.Parameters.Add(new SqlParameter
                                        {
                                            ParameterName = "@LanguageCode",
                                            SqlDbType = SqlDbType.Int,
                                            Value = item.LanguageCode,
                                        });
            #endregion

            _Adapter.InsertCommand = _Command;

            int contentId = 0;
            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();



                if (returnedValue != null)
                {
                    int.TryParse(returnedValue.ToString(), out contentId);
                }
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            return contentId;
        }

        #endregion

        #region Edit
        public static void EditNode(CustomSiteMapNode node)
        {
            // need to inser entity ....
            if (!node.IsContentNode)
            {
                if (node.Entity.ID == 0)
                {
                    node.Entity.ID = InsertEntity(node.EntityItem);
                    if (node.Entity.ID == 0)
                        return;

                    node.Url += node.Entity.ID;
                }
            }
            else
            {
                node.Entity.ID = 0;
                //node.Url += node.SiteMapNodeId;
            }

            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "EditSiteMapNode";

            #region Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@SiteMapNodeId",
                SqlDbType = SqlDbType.Int,
                Value = node.SiteMapNodeId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@TitleId",
                SqlDbType = SqlDbType.Int,
                Value = node.Title.ID,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Url",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.Url,
            });
            if (node.Entity.ID > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@EntityId",
                    SqlDbType = SqlDbType.Int,
                    Value = node.Entity.ID,
                });
            }
            if (node.EntityItem.ParentItemId > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@EntityParentItemId",
                    SqlDbType = SqlDbType.Int,
                    Value = node.EntityItem.ParentItemId,
                });
            }
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ParentSiteMapNodeId",
                SqlDbType = SqlDbType.Int,
                Value = node.ParentId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Level",
                SqlDbType = SqlDbType.Int,
                Value = node.Level,
            });
            if (node.EntityTypeId > 0)
            {
                _Command.Parameters.Add(new SqlParameter
                                            {
                                                ParameterName = "@EntityTypeId",
                                                SqlDbType = SqlDbType.Int,
                                                Value = node.EntityTypeId,
                                            });
            }
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@EnglishTitleName",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.TitleItem.EnglishName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@FrenchTitleName",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.TitleItem.FrenchName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ArabicTitleName",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.TitleItem.ArabicName,
            });

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@EnglishEntityName",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.EntityItem.EnglishName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@EnglishEntityDescription",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.EntityItem.EnglishDescription,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@FrenchEntityName",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.EntityItem.FrenchName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@FrenchEntityDescription",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.EntityItem.FrenchDescription,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ArabicEntityName",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.EntityItem.ArabicName,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ArabicEntityDescription",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.EntityItem.ArabicDescription,
            });

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@EnglishLanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = (int)LanguagesEnum.English,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ArabicLanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = (int)LanguagesEnum.Arabic,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@FrenchLanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = (int)LanguagesEnum.French,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@IsContentNode",
                SqlDbType = SqlDbType.Bit,
                Value = node.IsContentNode,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }

        public static void EditContent(SitemapNodeContent content)
        {

            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "EditSiteMapNodeContent";

            #region Parameters

            _Command.Parameters.Clear();


            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@SiteMapNodeContentId",
                SqlDbType = SqlDbType.Int,
                Value = content.SiteMapNodeContentId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@SiteMapNodeId",
                SqlDbType = SqlDbType.Int,
                Value = content.SiteMapNodeId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Content",
                SqlDbType = SqlDbType.NVarChar,
                Value = content.Content,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ContentTypeId",
                SqlDbType = SqlDbType.Int,
                Value = content.ContentTypeId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = content.LanguageCode,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }

        public static void EditSiblingNodes(List<CustomSiteMapNode> changedSiblingNodes)
        {
            foreach (var changedSiblingNode in changedSiblingNodes)
            {
                EditNode(changedSiblingNode);
            }
        }


        private static void EditNodeUrl(CustomSiteMapNode node)
        {
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "EditSiteMapNodeUrl";

            #region Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@SiteMapNodeId",
                SqlDbType = SqlDbType.Int,
                Value = node.SiteMapNodeId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@Url",
                SqlDbType = SqlDbType.NVarChar,
                Value = node.Url,
            });
            #endregion

            _Adapter.InsertCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteScalar();

                // Close Connection)
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }
        }
        #endregion

        public static KeyValuePair<string, string> GetNodeContent(int siteMapNodeId, int languageCode)
        {

            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "GetSiteMapNodeContent";

            #region Parametrers
            _Command.Parameters.Clear();
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@SiteMapNodeId",
                SqlDbType = SqlDbType.Int,
                Value = siteMapNodeId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@LanguageCode",
                SqlDbType = SqlDbType.Int,
                Value = languageCode,
            });
            #endregion

            _DataSet = new DataSet();
            _DataSet.Tables.Clear();
            _Adapter.SelectCommand = _Command;
            _Adapter.Fill(_DataSet);

            KeyValuePair<string, string> contentData = new KeyValuePair<string, string>();

            DataTable userDataTable = _DataSet.Tables[0];
            if (userDataTable != null && userDataTable.Rows.Count > 0)
            {
                if (userDataTable.Rows[0]["TitleName"] != null && userDataTable.Rows[0]["Content"] != null)
                {
                    contentData = new KeyValuePair<string, string>(userDataTable.Rows[0]["TitleName"].ToString(), userDataTable.Rows[0]["Content"].ToString());

                }

            }
            return contentData;
        }

        public static bool DeleteSiteMapNode(CustomSiteMapNode currentNode)
        {
            bool deleted = false;
            _Command.CommandType = CommandType.StoredProcedure;
            _Command.CommandText = "DeleteSiteMapNode";

            _Command.Parameters.Clear();

            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@SiteMapNodeId",
                SqlDbType = SqlDbType.Int,
                Value = currentNode.SiteMapNodeId,
            });
            _Command.Parameters.Add(new SqlParameter
            {
                ParameterName = "@TitleId",
                SqlDbType = SqlDbType.Int,
                Value = currentNode.Title.ID,
            });
            if ( currentNode.Entity!=null &&currentNode.Entity.ID>0)
            {
                _Command.Parameters.Add(new SqlParameter
               {
                   ParameterName = "@EntityId",
                   SqlDbType = SqlDbType.Int,
                   Value = currentNode.Entity.ID,
               }); 
            }
            _Adapter.DeleteCommand = _Command;

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                var returnedValue = _Command.ExecuteNonQuery();
                int rowsAffected;
                if ( int.TryParse(returnedValue.ToString(), out rowsAffected))
                {
                    deleted = rowsAffected > 0;
                }
                

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
                return false;
            }
            return deleted;
        }
    }
}
